package cn.itcast.dao;

import cn.itcast.utils.MyJdbcUtil;
import cn.itcast.vo.Customer;
import cn.itcast.vo.PageBean;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class ApacheDBUtilsCustomerDaoImpl implements CustomerDao {
	
	public int save(Customer c) {
		
		// http://blog.csdn.net/shuaihj/article/details/15378109
		QueryRunner runner = null;
		
		if (MyJdbcUtil.isSqlServer) {
			runner = new QueryRunner(MyJdbcUtil.getDataSource(), true);
		} else {
			runner = new QueryRunner(MyJdbcUtil.getDataSource());
		}
		
		String sql = "insert into t_customer values (?,?,?,?,?,?,?,?)";
		Object[] params = { c.getId(), c.getUsername(), c.getGender(), c.getBirthday(), c.getCellphone(), c.getEmail(),
				c.getLove(), c.getType() };
		try {
			return runner.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	public List<Customer> findAll() {
		QueryRunner runner = new QueryRunner(MyJdbcUtil.getDataSource());
		String sql = "select * from t_customer";
		try {
			return (List) runner.query(sql, new BeanListHandler(Customer.class));
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	public int delete(String id) {
		QueryRunner runner = new QueryRunner(MyJdbcUtil.getDataSource());
		try {
			return runner.update("delete from t_customer where id = ?", id);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	public Customer findById(String id) {
		QueryRunner runner = new QueryRunner(MyJdbcUtil.getDataSource());
		try {
			return (Customer) runner.query("select * from t_customer where id = ?", new BeanHandler(Customer.class),
					new Object[] { id });
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	public int update(Customer c) {
		QueryRunner runner = null;
		
		if (MyJdbcUtil.isSqlServer) {
			runner = new QueryRunner(MyJdbcUtil.getDataSource(), true);
		} else {
			runner = new QueryRunner(MyJdbcUtil.getDataSource());
		}
		
		String sql = "update t_customer set username = ?,gender=?,birthday=?,cellphone=?,email=?,love=?,type=? where id = ?";
		Object[] params = { c.getUsername(), c.getGender(), c.getBirthday(), c.getCellphone(), c.getEmail(),
				c.getLove(), c.getType(), c.getId() };
		try {
			return runner.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	public List<Customer> findByWhere(String username, String type) {
		QueryRunner runner = new QueryRunner(MyJdbcUtil.getDataSource());

		StringBuffer sb = new StringBuffer("select * from t_customer where 1=1 ");

		List<Object> list = new ArrayList();
		if ((username != null) && (!username.trim().isEmpty())) {
			sb.append(" and username = ? ");
			list.add(username);
		}
		if ((type != null) && (!type.trim().isEmpty())) {
			sb.append(" and type=? ");
			list.add(type);
		}
		try {
			return (List) runner.query(sb.toString(), new BeanListHandler(Customer.class), list.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	public PageBean<Customer> findByPage(int pageCode, int pageSize) {
		PageBean<Customer> page = new PageBean();
		page.setPageCode(pageCode);
		page.setPageSize(pageSize);

		QueryRunner runner = new QueryRunner(MyJdbcUtil.getDataSource());
		String countSql = "select count(*) from t_customer";
		try {
			long count = 0;
			if (MyJdbcUtil.isSqlServer) {
				 count = ((Integer) runner.query(countSql, new ScalarHandler())).intValue();
			} else if (MyJdbcUtil.isOracleServer || MyJdbcUtil.isInformixServer) {
				count = ((BigDecimal) runner.query(countSql, new ScalarHandler())).longValue();
			} else {
				 count = ((Long) runner.query(countSql, new ScalarHandler())).longValue();
			}

			page.setTotalCount((int) count);
			
			String selSql = "";
			
			int m, n;
			List<Customer> beanList;
			
			if (MyJdbcUtil.isSqlServer) {
				selSql = "select top %d * from t_customer where id not in (select top %d id from t_customer)";
				m = pageSize;
				n = Integer.valueOf((pageCode - 1) * pageSize);
				
				beanList = (List) runner.query(String.format(selSql, m, n), new BeanListHandler(Customer.class));
			} else if (MyJdbcUtil.isOracleServer) {
				selSql = "SELECT * FROM " +
				"(" +
				"SELECT A.*, ROWNUM RN " +  
				"FROM (SELECT * FROM t_customer) A " +
				"WHERE ROWNUM <= ?" +
				")" +
				"WHERE RN > ?";
				
				n = Integer.valueOf((pageCode - 1) * pageSize);
				m = n + pageSize;
				
				beanList = (List) runner.query(selSql, new BeanListHandler(Customer.class),
						new Object[] { m, n });
			}  else if (MyJdbcUtil.isPostgresServer) {
				selSql = "select * from t_customer limit ? offset ?";
				m = Integer.valueOf((pageCode - 1) * pageSize);
				n = Integer.valueOf(pageSize);
				
				beanList = (List) runner.query(selSql, new BeanListHandler(Customer.class),
						new Object[] { n, m });
			}  else if (MyJdbcUtil.isInformixServer) {
				selSql = "SELECT SKIP ? FIRST ? * FROM t_customer";
				m = Integer.valueOf((pageCode - 1) * pageSize);
				n = Integer.valueOf(pageSize);
				
				beanList = (List) runner.query(selSql, new BeanListHandler(Customer.class),
						new Object[] { m, n });
			} else {
				selSql = "select * from t_customer limit ?,?";
				m = Integer.valueOf((pageCode - 1) * pageSize);
				n = Integer.valueOf(pageSize);
				
				beanList = (List) runner.query(selSql, new BeanListHandler(Customer.class),
						new Object[] { m, n });
			}

			page.setBeanList(beanList);

			return page;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
}
